# -*- coding: utf-8 -*-

"""A script to process items from a redis queue."""
from __future__ import print_function, unicode_literals
import re
import argparse
import json
import logging
import pprint
import sys
import time
# import MySQLdb
# import MySQLdb.cursors
import pymysql
import codecs
import pymysql.cursors
import logging
from scrapy_redis import get_redis

logger = logging.getLogger('process_items')
flag = 0

def process_items(r, keys, timeout, limit=0, log_every=1000, wait=.1):
    regex = re.compile("(.*?):.*")
    table = regex.findall(keys[0])[0]
    """
    Process items from a redis queue.

    Parameters
    ----------
    r : Redis
        Redis connection instance.
    keys : list
        List of keys to read the items from.
    timeout: int
        Read timeout.
    """
    limit = limit or float('inf')
    processed = 0
    while processed < limit:
        # Change ``blpop`` to ``brpop`` to process as LIFO.
        ret = r.blpop(keys, timeout)
        # If data is found before the timeout then we consider we are done.
        if ret is None:
            logger.info("Waiting Items!!!")
            time.sleep(wait)
            continue
	
        source, data = ret
        try:
            item = json.loads(data)
        except Exception:
            logger.exception("Failed to load item:\n%r", pprint.pformat(data))
            continue

        try:
            conn = pymysql.connect(host='127.0.0.1', user='root', passwd='', db = 'jobs', port=3306,charset="utf8")
            if(conn is None):
                logger.error('mysql cannot connect successed')
            cur = conn.cursor()
            global flag
            if 'job' in table:    
                if flag == 0:
                    cur.execute("CREATE DATABASE if not exists jobs")
                    cur.execute("USE jobs")
                    sql = "create table if not exists job2(id int primary key auto_increment,title varchar(100), url varchar(100), company varchar(100), location varchar(150),minoffer double,maxoffer double, city varchar(30), work_experience varchar(30),education varchar(40),trades varchar(100),companyType varchar(80),companySize varchar(30)) DEFAULT CHARSET=utf8;"
                flag = 1
                cur.execute(sql)
                sql1 = "insert into job2"
                sqlstr="".join([sql1,"(title,url,company,location,minoffer,maxoffer,city,work_experience,education,trades,companyType,companySize) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"])
                value = [item['title'],item['url'],item['company'],item['location'],item['minoffer'],item['maxoffer'],item['city'],item['work_experience'],item['education'],item['trades'],item['companyType'],item['companySize']]
                cur.execute(sqlstr,value)
                logging.info("----------------职位信息写入数据库成功-----------------")
                conn.commit()
                cur.close()
        except KeyError:
            logger.exception("[%s] Failed to process item:\n%r",
                             source, pprint.pformat(item))
            continue

        processed += 1
        if processed % log_every == 0:
            logger.info("Processed %s items", processed)


def main():
    reload(sys)
    sys.setdefaultencoding('utf-8')
    parser = argparse.ArgumentParser(description=__doc__)
    
    parser.add_argument('key', help="Redis key where items are stored")
    parser.add_argument('--host')
    parser.add_argument('--port')
    parser.add_argument('--timeout', type=int, default=5)
    parser.add_argument('--limit', type=int, default=0)
    parser.add_argument('--progress-every', type=int, default=100)
    parser.add_argument('-v', '--verbose', action='store_true')
    
    args = parser.parse_args()
    params = {}
    if args.host:
        params['host'] = args.host
    if args.port:
        params['port'] = args.port

    logging.basicConfig(level=logging.DEBUG if args.verbose else logging.INFO)
   
    r = get_redis(**params)
    host = r.connection_pool.get_connection('info').host
    logger.info("Waiting for items in '%s' (server: %s)", args.key, host)
    kwargs = {
        'keys': [args.key],
        'timeout': args.timeout,
        'limit': args.limit,
        'log_every': args.progress_every,
    }
    try:
        process_items(r, **kwargs)
        retcode = 0  # ok
    except KeyboardInterrupt:
        retcode = 0  # ok
    except Exception:
        logger.exception("Unhandled exception")
        retcode = 2

    return retcode

if __name__ == '__main__':
    sys.exit(main())
